package cn.group_06.bookmanager.dao;
import javax.swing.JOptionPane;
import cn.group_06.bookmanager.model.cgzib;
import cn.group_06.bookmanager.utils.toolUtil;
import com.mysql.cj.util.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class cgzibDao {
	

	
	public int addUser(Connection con, cgzib user) throws Exception{
		//鏌ヨ娉ㄥ唽鐢ㄦ埛鍚嶆槸鍚﹀瓨鍦�
		String sql = "select * from cgzib where xsqdh=? ";

	    sql="insert into cgzib (qdh,spbh,sl,dj,zj,bz,id) values (?,?,?,?,?,?,?)";
	    PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
		pstmt2.setString(1, user.getQdh());
		pstmt2.setString(2, String.valueOf(user.getSpbh()));
		pstmt2.setString(3,  String.valueOf(user.getSl()) );
		pstmt2.setString(4,  String.valueOf(user.getDj()) );
		pstmt2.setString(5,  String.valueOf(user.getZj()) );
		pstmt2.setString(6,  String.valueOf(user.getBz()) );
		pstmt2.setString(7,  String.valueOf(user.getId()) );
		return pstmt2.executeUpdate();
	}
	
	
	public ResultSet list(Connection con,cgzib user)throws Exception{
		StringBuffer sb=new StringBuffer("select * from cgzib ");

		if(!StringUtils.isNullOrEmpty(user.getQdh())){
			sb.append(" where qdh like '%"+user.getQdh()+"%'");
		}
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}
	

public int update(Connection con,cgzib user) throws Exception{
		String sql="update cgzib set qdh=?,spbh=?,sl=? ,dj=?,zj=?,bz=? where id=?";
		PreparedStatement pstmt2=(PreparedStatement) con.prepareStatement(sql);
		
		pstmt2.setString(1, String.valueOf(user.getQdh()));
		pstmt2.setString(2, String.valueOf(user.getSpbh()));
		pstmt2.setString(3,  String.valueOf(user.getSl()) );
		pstmt2.setString(4,  String.valueOf(user.getDj()) );
		pstmt2.setString(5,  String.valueOf(user.getZj()) );
		pstmt2.setString(6,  String.valueOf(user.getBz()) );
		
		
		pstmt2.setInt(7, user.getId());
		return pstmt2.executeUpdate();
	}


	
	
	public int del(Connection con,cgzib user)throws Exception{
		String sql="delete from cgzib  where id=?";
		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sql);
		pstmt.setInt(1, user.getId());
		return pstmt.executeUpdate();
	}



	public void WriteExcel(List<cgzib> list, String filepath) throws IOException {

		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("閲囪喘鏄庣粏淇℃伅");
		HSSFRow row = sheet.createRow(0);
		String[] s = { "num", "qdh", "spbh", "sl", "dj","zj","bz" };
		for (int i = 0; i < 7; i++) {
			row.createCell(i).setCellValue(s[i]);

		}
		for (int j = 0; j < list.size(); j++) {
			HSSFRow r = sheet.createRow(j + 1);
			r.createCell(0).setCellValue(list.get(j).getId());
			r.createCell(1).setCellValue(list.get(j).getQdh());
			r.createCell(2).setCellValue(list.get(j).getSpbh());
			r.createCell(3).setCellValue(list.get(j).getSl());
			r.createCell(4).setCellValue(list.get(j).getDj());
			r.createCell(5).setCellValue(list.get(j).getZj());
			r.createCell(6).setCellValue(list.get(j).getBz());

		}
		OutputStream out = new FileOutputStream(filepath);
		workbook.write(out);
		System.out.println("瀵煎嚭鎴愬姛锛�");
		out.close();
	}
	//如果死规定某个数改
	public ResultSet listLessThanQuantity(Connection con, int quantity) throws Exception {
	    String sql = "SELECT * FROM cgzib WHERE sl < ?";
	    PreparedStatement pstmt = con.prepareStatement(sql);
	  //
	    pstmt.setInt(1, quantity); // 设置查询条件为数量小于 quantity
	    //
	    return pstmt.executeQuery();
	}
	
	
}
